package com.edu.nf.handle;

import com.edu.nf.interfaces.ResultSetHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SQLExecutor {
    private Connection conn;

    //自动关闭
    private boolean autoClass=true;

    public SQLExecutor(Connection conn){
        this.conn = conn;
    }

    public Connection getConn() {
        return conn;
    }

    /**
     * 执行查询
     * @param sql
     * @param params
     * @return
     */
    public <T>T executeQuery(String sql, ResultSetHandler<T> handler, Object... params) throws SQLException {
        if (conn == null) {
            throw new SQLException("null connection");
        }

        if (sql == null) {
            close();
            throw new SQLException("null sql");
        }

        if (handler == null){
            close();
            throw new SQLException("null ResultSetHandler");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t = null;
        try {
            ps = conn.prepareStatement(sql);
            setParameters(ps,params);
            rs = ps.executeQuery();
            t = handler.handle(rs);
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }finally {
            close(rs);
            close(ps);
            if (autoClass){
                close();
            }
        }
        return t;
    }

    /**
     * 执行增删改操作
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public int executeUpdate(String sql,Object... params) throws SQLException{
        if (conn == null){
            throw new SQLException("Null Connection");
        }

        if (sql == null){
            close();
            throw new SQLException("Null SQL语句");
        }

        PreparedStatement ps = null;
        int i = 0;
        try{
           ps = conn.prepareStatement(sql);
           setParameters(ps,params);
           i = ps.executeUpdate();
        }catch (Exception e){
            System.out.println("更新失败："+e.getMessage());
        }finally {
            close(ps);
            if (autoClass){
                close();
            }
        }
        return i;
    }


    /**
     * 批量操作
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public int[] executeBatch(String sql,Object[][] params) throws SQLException{
        if (conn == null){
            throw new SQLException("Null Connection");
        }

        if (sql == null){
            close();
            throw new SQLException("Null SQL");
        }

        PreparedStatement ps = null;
        int[] rows = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i=0;i<params.length;i++){
                setParameters(ps,params[i]);
                ps.addBatch();
            }
            rows = ps.executeBatch();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            close(ps);
            if(autoClass){
                close();
            }
        }
        return rows;
    }


    /**
     * 关闭连接
     */
    private void close(){
        try {
            if (conn!=null&&!conn.isClosed()){
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * 关闭结果集
     */
    private void close(ResultSet rs){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭prepareStatement
     */
    private void close(PreparedStatement ps){
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置sql语句的参数
     */
    private void setParameters(PreparedStatement ps,Object[] params) throws SQLException{
        for (int i=0;i<params.length;i++){
            ps.setObject(i+1,params[i]);
        }
    }

}
